import sqlalchemy
from sqlalchemy import create_engine , ForeignKey
from sqlalchemy import Column, Integer, String, Text 
from sqlalchemy.orm import sessionmaker, relation, backref 
from pprint import  pprint
from sqlalchemy.ext.declarative import declarative_base 

from sqlalchemy import UniqueConstraint

import validate

# TODO: change table names to simple plurals

#===============================================================================
# GLOBALS
#===============================================================================
Base = declarative_base()
engine = create_engine('sqlite:///form_1.db', echo=False)
Session = sessionmaker(bind=engine)
s = Session()
metadata = Base.metadata

#===============================================================================
# ACCESS
# Class consists of helper functions to simplify accessing data objects with
# simpler method/function calls
#===============================================================================

def session_open():
    Session = sessionmaker(bind=engine)
    return Session()

def commit():
    try:
        s.commit()
        content = 'OK'
        valid = True   
    except sqlalchemy.exc.InvalidRequestError, e:
        content = e.args
        valid = False
    except sqlalchemy.exc.IntegrityError, e:
        content =  e.args
        content = 'A record with this name or unique identifier already exists'
        valid = False

    return valid, content
    
class Access():
    def save(self):
        '''If object has a "name" field, then validate it''' 
        valid = True
        content = self
        if 'name' in self.__dict__.keys():
            valid, content = validate.name(self.name)
        if valid:
            s.add(self)
        #TODO validate that names are text no spaces
        return valid, content
        
    def read(self):
        '''Reads object from db and  returns it. Requires that a "name" column 
        is used as unique identifier.'''
  
        filter_string = '{0}.name="{1}"'.format(self.__table__, self.name)
        # return s.query(self.__class__).filter(filter_string).one()
        try:
            object = s.query(self.__class__).filter(filter_string).one()
        except sqlalchemy.orm.exc.NoResultFound, e:
            object = None
        return object
        
    def all(self):
        return s.query(self.__class__).all()
    
    def delete(self):
        s.delete(self)
        
    def select(self, selection):
        '''Returns a selection of objects. Examples of filters are:
        "_group.id < 4"
        '''
        return s.query(self.__class__).filter(selection).all()

            
    def exists(self):
        '''Returns true if selection returns one or more records'''
        filter_string = '{0}.name="{1}"'.format(self.__table__, self.name)
        return bool(s.query(self.__class__).filter(filter_string).count() > 0)

    def create(self):
        #TEST: the create function
        if self.exists():
            return False, "A record with this name or other unique identifier already exists"
        else:
            return self.save()
        
    def add(self,type,id):
        '''General method for linking one object to another'''
#        print 5, self.__repr__()
        valid = False
        content = 'Method "add" not implemented for adding {0} to {1}'.format(
                                                self.__class__.__name__, type)
        if self.__class__.__name__ == 'User':# Adding a user to a groups
            if type == 'Group':
                #group = Group(name).read()
                u2g = User2Group(self.id, id)
                valid, content = u2g.save()       
        return valid, content
            
#            print 40, self.__class__ == '__main__.User' 
#            if type(self) == 'User':
#            pass
#        
#        u2g = User2Group(self.id, group.id)
#        valid, content = u2g.save()
#        if valid:
#            valid, content = commit()
#        return valid, content
        
#===============================================================================
#    
# DATA MODEL 
# 
#===============================================================================


    
class User(Base, Access):
    __module__ = __name__
    __tablename__ = 'user'
    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(24), unique = True) 
    email = Column('email', String(255))
    password = Column('password',String(255))
 
    def __init__(self, name=None):
        self.name = name
    def __repr__(self):
        return '<User %s "%s">' % (self.id, self.name)

    # TODO: delete this function
    def addGroup(self, group):
        #TODO: change to format User.add(Group , 'fred')
        ''' Adds the user to a group. Takes group as object and returns "valid",
        "content"'''
        u2g = User2Group(self.id, group.id)
        valid, content = u2g.save()
        if valid:
            valid, content = commit()
        return valid, content
    
    
    def Groups(self):
        '''Returns the groups that a user belongs to.'''
        #TODO: Make sure it's only active records returned
        return s.query(Group).join(User2Group).join(User).filter(User.id == 
                                                                 self.id).all()
       
class Group(Base, Access):
    __module__ = __name__
    __tablename__ = '_group'
    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(24), unique = True, nullable=False)
    
    def __init__(self, name=None):
        self.name = name

    def __repr__(self):
        return '<Group %s "%s">' % (self.id, self.name)
    
class User2Group(Base, Access):
    __module__ = __name__
    __tablename__ = 'user2group'
    __table_args__ = (UniqueConstraint('user_id', 'group_id'), {} ) 

    id = Column('id', Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relation(User, order_by='User.id', backref="user")    
    group_id = Column(Integer, ForeignKey('_group.id'))
    group = relation('Group', order_by='Group.id', backref="group")

    
    def __init__(self, user_id=None, group_id=None):
        self.user_id = user_id
        self.group_id = group_id

    def __repr__(self):
        return "<User2Group %s %s>" % (self.user_id, self.group_id)

if __name__ == '__main__':
 
###  CREATE TABLES / SESSION
    metadata.create_all(engine)
    import util
    from pprint import pprint
#===============================================================================
#    User2Group
#===============================================================================
    


#    print Group().all()[0]
#    
#    create 2 new groups
    group_name = util.unique_name('grp')
    group_name = 'grp_unique'
    G = Group(group_name)
    group_name2 = util.unique_name('grp')
    G2 = Group(group_name2)
    G.save()
    G2.save()
    commit()
    G = Group(group_name).read()
    G2 = Group(group_name2).read()
    G3 = Group('-1').read()
    print 20, G, G2, G3
#    
#    U = User('usr_unique').read()
#    valid, content = U.addGroup('Group',G.id)
#    if not valid: print 'Error adding group:', content
#    valid, content = U.add('Group', G2.id)
#    if not valid: print 'Error adding group:', content
#    valid, content = U.add('Group', 'fred')
#    if not valid: print 'Error adding group:', content
#    commit()
#    print 30
#    pprint(U.Groups())
#    print 40
#    pprint(User2Group().all())
#    print 50
#    for x in User2Group().all():
#        print '     ', x.user_id, type(x.group_id), type(x)
#    print 30, User2Group().all()
#    
    
#    u.add('Group', 'b')
#    print 20, type(u)
#    print 22, u.__class__.__name__
#    print 23, dir(u.__class__)
#    pprint(dir(u))
#    g1 = Group(Group().all()[1].name).read()
#    g2 = Group(Group().all()[2].name).read()
#    u.addGroup(g1)
#    u.addGroup(g2)

#    print 10 * '='
#    pprint(s.query(User2Group).all())
    
    
#    u.addGroup(g2)
#    commit()
#    pprint(User().all())
#    print g2
#    g = Group('grp_unique').read()
#    # print u

##    print g
#    u.addGroup(g)
#    commit()
#    pprint(User().all())

# TEST: test adding None as Group to User2Group
##===============================================================================
## GROUPS  
##===============================================================================



# Add a Group
#    Group(util.unique_name('grp')).save()
    # Group('grp_unique').save()
#    commit()
### Read a Group
#    print 40, Group("grp_unique").read()
## Read all Groups    
#    print 50, len(Group().all())
#    print 60, len(Group().select(None))  
#    pprint(Group().all())

#    print 62, Group('nevernever').read()

### Read selected groups    
#    print 70, Group().select("_group.id < 4")
#
### checking that delete works    
#    before = len(Group().all())
#    Group('grp_temp').save()
#    print 72
#    commit()
#    after = len(Group().all())
#    print 73
#    Group('grp_temp').read().delete()
#    print 74
#    commit()
#    print 75
#    print 76, len(Group().all())
#    restored = len(Group().all())
#    print 80, before, after, restored
#
### Handle a duplicate group
#    Group('grp_unique').save()
#    print 85
#    error, message = commit()
#    if error: print message


    
##===============================================================================
## Users
##===============================================================================
## Add a User
#    u = util.unique_name('usr')
#    # u = util.unique_name('usr_unique')
#    u = 'usr_unique'
#    User(u).save()
#    # User('usr_unique').save()
#    commit()
#    print 'added user', User(u).read()
### Read a User ###
#    print 90, User("usr_unique").read()
#    #Read a User that doesn't exist'''
#    print 92, User("not").read()
    
    
## Read all Users    

    print 110, len(User().select(None))  
#
### Read selected users    
#    print 120, User().select("user.id < 4")
#
### checking that delete user works    
#    before = len(User().all())
#    print 121
#    User('usr_temp').save()
#    commit()
#    print 122
#    after = len(User().all())
#    User('usr_temp').read().delete()
#    commit()
#    print 125
#    restored = len(User().all())
#    print 130, before, after, restored   
#    
#### Handle a duplicate user
##    User('usr_unique').save()
##    print 140
##    error, message = commit()
##    if error: print message


#    print 301, s.query(User).filter('User.name="usr_unique"').count()
#    print 302, User('usr_unique').exists()
#    print 305, s.query(User).filter('User.name="usr_unique_xxxx"').count()
#    print 307, User('usr_unique_xxxx').exists()

#### checking that delete user works    
#    print 300, Group('grp_temp').exists()
#    before = len(Group().all())
#    print 310
#    print Group('grp_temp').save()
#    print 312, Group('grp_temp').exists()
#    commit()
#    assert len(Group().all()) == before + 1
#    print 320
#    print 322, User('grp_temp').read()
#    commit()
#    print 330
#    assert len(Group().all()) == before

# add a user to a group
    
# TEST: check that a call to Group().all() doesnt fail when no groups exist 